import pandas as pd
import pymysql
from DBUtils.PooledDB import PooledDB

MysqlPool = PooledDB(creator=pymysql,  mincached=1, maxcached=6, host="139.196.174.131", port=3306,\
                    user="bozai", passwd="hellobobo", db='easy_refiner_step4_data_files', charset="utf8")

def read_data_from_mysql(conn, sqli, col_names=None):
    '''
    #conn: 数据库连接对象
    #sqli: sql语句
    #col_names: 需要提取的字段名
    #return: 数据表中指定字段的数据
    '''
    cursor = conn.cursor(pymysql.cursors.SSCursor)    #使用cursor()方法获取操作游标
    # 查询
    try:
        # print(sqli)
        # 执行SQL语句
        cursor.execute(sqli)
        # 获取所有记录列表
        data_mysql = []
        while True:
            row = cursor.fetchone()
            if not row:
                break
            data_mysql.append(row)
        print(data_mysql)
        data_mysql = pd.DataFrame(data_mysql, columns=col_names)
        cursor.close()  #关闭指针对象
        return data_mysql
    except Exception as err:
        print(repr(err))
        conn.close()
        print ("Error: unable to fecth data")

if __name__ == '__main__':
    from time import time
    project_id = 3841
    connect = MysqlPool.connection()      #connect_mysql(database_name='easy_refiner_management')
    #从数据库读取所有数据表的信息，提取两列数据'file_data_table_name','file_header'
    # info_col_names = ['file_id','file_data_table_name','file_header','file_row_num']
    # sql_info = "SELECT %s FROM %s WHERE project_id=%d"%(','.join(info_col_names), 'easy_refiner_management.step4_file', project_id)
    # sql_info = "SELECT %s FROM %s"%(','.join(info_col_names), 'easy_refiner_management.step4_file')

    database_name = 'easy_refiner_step3_data_files'
    table_name = '2018_03_14_07_46_39id4460'
    sql_info = "SELECT %s FROM %s"%('*', database_name+'.'+table_name)

    beg = time()
    table_info = read_data_from_mysql(conn=connect, sqli=sql_info)
    print(table_info)
    print('time cost:%0.4f'%(time() - beg))